import datetime
from sqlalchemy import create_engine
import pandas as pd


class DataBase:
    def __init__(self, idy="sw_l1", td=""):
        super(DataBase, self).__init__()
        if len(str(td)) < 5:
            self.today = datetime.date.today()
        elif len(str(td)) == 10:
            t = pd.to_datetime(td)
            self.today = datetime.date(year=t.year, month=t.month, day=t.day)
        else:
            self.today = td
        self.today_str = f"a{str(self.today)[:10]}"
        self.idy = idy
        self.engine = create_engine(
            f'mysql+pymysql://hf_user:290202@192.168.3.179:3306/{idy}?charset=utf8&use_unicode=1')
        self.map = {"sw_l1": "申万一级行业日度更新", "sw_l2": "申万二级行业日度更新"}
        print("ini……", f"you selected {self.map[self.idy]}")

    def upload_today_df(self):
        address = f'../../output/{self.today}{self.map[self.idy]}.xlsx'
        print('uploading ---', address)
        self.upload_df_by_address(address)

    def upload_df_by_address(self, address):
        try:
            df = pd.read_excel(address, index_col=0)
            self.upload_df(df)
        except FileNotFoundError as e:
            print(e, address, "not found")

    def upload_df(self, df):
        df['date'] = df.index
        df.index = list(range(len(df)))
        rp_today_str = self.today_str.replace("-", '_')
        existed_tbs = pd.read_sql("show tables", self.engine).iloc[:, 0].to_list()
        if rp_today_str not in existed_tbs:
            df.to_sql(rp_today_str, self.engine, if_exists='replace', index=True)
            print("save sucess!!", f"db in {self.idy} table in {rp_today_str}")
            print("今日已上传！")
        else:
            print("检查历史上传版本！")
            old_df = self.download_today_df()
            if len(df) > len(old_df):
                df.to_sql(rp_today_str, self.engine, if_exists='replace', index=True)
                print(f"append today df from {len(old_df)} to {len(df)}")
            else:
                print("数据库版本为最新，无需上传！")

    def download_df_by_name(self, db_name):
        data = pd.read_sql(f"select * from {db_name}", self.engine)
        print('data shape', data.shape)
        print("latest date from sql db >>", data.date.to_list()[-1])
        data.index = data['date']
        return data.iloc[:, 1:-1]

    def download_today_df(self):
        rp_today_str = f"{self.today_str}".replace("-", "_")
        return self.download_df_by_name(rp_today_str)

    def download_last_day_df(self):
        last_day = self.today - datetime.timedelta(days=1)
        last_day_str = str(last_day)[:10]
        rp_last_day_str = 'a' + last_day_str.replace("-", "_")
        self.download_df_by_name(rp_last_day_str)


if __name__ == "__main__":
    db = DataBase("sw_l1", '2021-12-11')
    db.upload_today_df()
    # td_data = db.download_today_df()
